# _*_ coding:utf-8 _*_
# @Time  : 2022.07.15
# @Author: zizlee
import json
import pathlib
from collections import Counter
from typing import Optional
from fastapi import APIRouter, Header, Body, UploadFile, Form, Query
from pydantic import BaseModel, ValidationError, datetime_parse, validator, conint, constr
from fastapi.encoders import jsonable_encoder
from hutool import security
from db import FAConnection
from v1_all_api import constant
from v1_all_api.all_utils import file_utils, datetime_utils
from v1_all_api.all_response import AllResponse
from configs import FILE_STORAGE

from . import work_utils

article_writing_api = APIRouter()


@article_writing_api.get('/')  # 查询文章撰写记录
def query_special_article(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                          page: int = Query(1, ge=1), page_size: int = Query(10, ge=5),
                          user: int = Query(0, ge=0), exm: int = Query(None, ge=-1, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    user_id = 'NOUSER' if not user else user
    examine_state = "NOEXM" if exm is None else exm
    query_sql = """
        SELECT a.id,a.create_date,a.user_id,a.title,a.article_type,a.rough_type,a.words,a.examine_state,
            a.examiner,a.examine_explain,a.allowance,a.partner,a.annex,a.annex_url,a.is_publish,a.note,
            a.level_flag,a.level_explain,
            b.ruizy_code,b.admin_name   
        FROM work_article_writing As a 
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.create_date>=%s AND a.create_date<=%s AND a.is_active=1 
        AND IF('NOUSER'=%s,TRUE,a.user_id=%s) 
        AND IF('NOEXM'=%s,TRUE,a.examine_state=%s) 
        ORDER BY create_date DESC,id DESC;
    """
    query_prm = [start_date, end_date, user_id, user, examine_state, examine_state]
    db_conn = FAConnection()
    ret = db_conn.query_paginator(query_sql, param=query_prm, page=page, page_size=page_size)
    for item in ret['data']:
        item['examine_zh'] = constant.EXAMINE_STATES.get(item['examine_state'])
    return AllResponse.operate_successfully(data=ret)


@article_writing_api.get('/groupExamine/')  # 查询组员下的文章
def query_group_article(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    # 查询用户小组下的组员
    group_sql = """
        SELECT id,user_id,group_en,is_leader,admin_name FROM work_user_extend 
        WHERE group_en=(SELECT group_en FROM work_user_extend WHERE user_id=%s) 
        AND end_date>=%s;
    """
    db_conn = FAConnection()
    group_users = db_conn.query(group_sql, param=[person['uid'], start_date], keep_conn=True)
    # 验证查询者是不是组长
    is_leader = False
    for user in group_users:
        if user['is_leader'] == 1 and user['user_id'] == person['uid']:
            is_leader = True
            break
    if not is_leader:
        db_conn.close()
        return AllResponse.operate_successfully(data={'articles': [], 'special_articles': []})
    # 分页查询组员文章记录
    query_sql = """
        SELECT a.id,a.create_date,a.user_id,a.title,a.article_type,a.rough_type,a.words,a.examiner,a.allowance,
            a.partner,a.annex,a.annex_url,a.is_publish,a.note,a.examine_state,a.examine_explain,a.examiner,a.level_flag,
            a.level_explain,b.ruizy_code,b.admin_name   
        FROM work_article_writing As a 
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.user_id IN (%s) AND a.create_date>=%s AND a.create_date<=%s AND a.is_active=1 
        ORDER BY create_date DESC,id DESC;
    """ % (','.join([str(u['user_id']) for u in group_users]), start_date, end_date)
    records = db_conn.query(query_sql)

    articles = []
    special_articles = []
    for art in records:
        art['article_type_zh'] = constant.ARTICLE_TYPES.get(art['article_type'])
        art['examine_zh'] = constant.EXAMINE_STATES.get(art['examine_state'])
        if art['article_type'] == 'T01':  # 读取专题文章
            special_articles.append(art)
        else:
            articles.append(art)
    response_data = {
        'articles': articles,
        'special_articles': special_articles  # 专题文章单独评级，所以分开
    }
    return AllResponse.operate_successfully(data=response_data)


@article_writing_api.get('/levelExamine/')  # 查询文章待评级
def query_special_article(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                          user: int = Query(0), exm: int = Query(None)):
    # exm: (None,全部);(0,等待审核);(1,审核通过);(-1,审核不通过)
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    # 查询日期内的文章
    sql = """
        SELECT a.id,a.create_date,a.user_id,a.title,a.article_type,a.rough_type,a.words,a.examiner,a.allowance,
            a.partner,a.annex,a.annex_url,a.is_publish,a.note,a.examine_state,a.examine_explain,a.examiner,a.level_flag,
            a.level_explain,b.ruizy_code,b.admin_name   
        FROM work_article_writing As a 
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.create_date>=%s AND a.create_date<=%s AND a.is_active=1 
        ORDER BY a.create_date DESC,id DESC;
    """
    prm = (start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    if user > 0:  # 过滤用户
        records = list(filter(lambda x: x['user_id'] == user, records))
    if exm is not None:
        records = list(filter(lambda x: x['examine_state'] == exm, records))
    articles = []
    special_articles = []
    for art in records:
        art['article_type_zh'] = constant.ARTICLE_TYPES.get(art['article_type'])
        art['examine_zh'] = constant.EXAMINE_STATES.get(art['examine_state'])
        if art['article_type'] == 'T01':
            special_articles.append(art)
        else:
            articles.append(art)
    response_data = {
        'articles': articles,
        'special_articles': special_articles  # 专题文章单独评级，所以分开
    }
    return AllResponse.operate_successfully(data=response_data)


class ExamineItem(BaseModel):
    aid: int
    examine_state: conint(ge=-1, le=1)
    examine_explain: constr(min_length=1)


class LevelItem(BaseModel):
    aid: int
    level_flag: str
    level_explain: constr(min_length=1)

    @validator('level_flag')
    def validate_level_flag(cls, value):
        if value not in ['A', 'B', 'C', 'D', 'E']:
            raise ValidationError('level not allowed!')
        return value


@article_writing_api.put('/groupExamine/')  # 审核组员的文章
def examine_group_article(ruizy_token: str = Header(...), examine: ExamineItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 查询名称
    operator_sql = "SELECT id,admin_name,is_leader FROM work_user_extend WHERE user_id=%s;"
    db_conn = FAConnection()
    operator = db_conn.query(operator_sql, param=[person['uid']], keep_conn=True, fetchone=True)[0]
    if not operator or operator['is_leader'] < 1:
        db_conn.close()
        return AllResponse.forbidden_error(msg='您暂无权限这样操作！')
    # 审核
    examine_sql = "UPDATE work_article_writing SET examine_state=%s,examine_explain=%s,examiner=%s WHERE id=%s;"
    _, success = db_conn.execute(examine_sql, param=[examine.examine_state, examine.examine_explain,
                                                     operator['admin_name'], examine.aid])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@article_writing_api.put('/levelExamine/')  # 评级文章
def examine_article_level(ruizy_token: str = Header(...), level: LevelItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 查询名称
    operator_sql = "SELECT id,admin_name FROM ruizy_user WHERE id=%s;"
    db_conn = FAConnection()
    operator = db_conn.query(operator_sql, param=[person['uid']], keep_conn=True, fetchone=True)[0]
    if not operator:
        db_conn.close()
        return AllResponse.forbidden_error(msg='您暂无权限这样操作！')
    # 审核
    examine_sql = "UPDATE work_article_writing SET level_flag=%s,evaluator=%s,level_explain=%s WHERE id=%s;"
    _, success = db_conn.execute(examine_sql, param=[level.level_flag, operator['admin_name'], level.level_explain,
                                                     level.aid])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class ArticleItem(BaseModel):
    create_date: datetime_parse.date
    title: str
    article_type: str
    media_name: str
    rough_type: str
    words: conint(ge=1)
    allowance: float
    partner: str
    is_publish: int
    note: str

    @validator('article_type')
    def validate_article_type(cls, value):
        if value not in constant.ARTICLE_TYPES.keys():
            raise ValidationError('article_type not allowed!')
        return value


@article_writing_api.post('/my/')  # 添加一个文章撰写记录
async def create_special_article(ruizy_token: str = Header(...), annex_file: UploadFile = Form(None),
                                 body_data: str = Form(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    try:
        body_content = json.loads(body_data)
        body_item = ArticleItem(**body_content)
    except json.decoder.JSONDecodeError:
        return AllResponse.validate_error(msg='parse body_data Error,it must be JSON string')
    except ValidationError as e:
        return AllResponse.validate_error(msg='参数错误！{}'.format(e))
    if not all([body_item.title, body_item.article_type]):
        return AllResponse.validate_error(msg='标题和类型为必填项！')
    article_obj = jsonable_encoder(body_item)
    article_obj['create_date'] = body_item.create_date.strftime('%Y%m%d')
    article_obj['user_id'] = person['uid']
    article_obj['annex'] = ''
    article_obj['annex_url'] = ''
    article_obj['examine_state'] = 0
    article_obj['examine_explain'] = ''

    if article_obj['article_type'] == 'T01':  # 如果是专题文章通过组审核，否则无法统计
        article_obj['examine_state'] = 1
        article_obj['examine_explain'] = '专题文章直接通过'
    else:
        # 查询发布人员发布时刻是否有小组，没小组直接审核通过，否则无法统计
        with FAConnection() as cursor:
            cursor.execute('SELECT id FROM work_user_extend WHERE user_id=%s AND end_date>=%s LIMIT 1;',
                           (person['uid'], datetime_utils.today()))
            group = cursor.fetchone()
        if not group:  # 发布人员不属于小组
            article_obj['examine_state'] = 1
            article_obj['examine_explain'] = '非小组成员直接通过'

    save_path = None
    if annex_file:  # 有附件
        save_path, sql_path = file_utils.get_file_paths(module_name='ArticleWriting', filename=annex_file.filename,
                                                        hashed=True)
        # 查看后缀
        if pathlib.Path(annex_file.filename).suffix != '.docx':
            return AllResponse.validate_error(msg='附件格式不正确！请使用docx格式')
        # 保存附件到指定文件夹
        file_content = await annex_file.read()
        with open(save_path, 'wb') as fp:
            fp.write(file_content)
        await annex_file.close()
        article_obj['annex'] = annex_file.filename
        article_obj['annex_url'] = sql_path
    # 保存记录到数据库
    sql = """
        INSERT INTO work_article_writing (create_date,user_id,title,article_type,media_name,rough_type,words,
        allowance,partner,note,annex,annex_url,is_publish,examine_state,examine_explain) VALUES 
        (%(create_date)s,%(user_id)s,%(title)s,%(article_type)s,%(media_name)s,%(rough_type)s,%(words)s,
        %(allowance)s,%(partner)s,%(note)s,%(annex)s,%(annex_url)s,%(is_publish)s,%(examine_state)s,%(examine_explain)s);
    """
    db_conn = FAConnection()
    success, _ = db_conn.insert(sql, param=article_obj)
    if not success and save_path:
        annex = pathlib.Path(save_path)
        if annex.is_file():
            annex.unlink()
    return AllResponse.create_successfully() if success else AllResponse.server_error()


@article_writing_api.get('/my/')  # 获取我的文章撰写记录
def query_special_article(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    sql = """
        SELECT id,create_date,title,article_type,media_name,rough_type,words,examiner,allowance,partner,note,
            examine_state,examine_explain,level_flag,level_explain,annex,annex_url,is_publish  
        FROM work_article_writing 
        WHERE user_id=%s AND create_date>=%s AND create_date<=%s 
        ORDER BY create_date DESC,id DESC;
    """
    prm = (person['uid'], start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    # 统计一下当前月的数据
    today = datetime_utils.today(obj=True)
    month_start, month_end = datetime_utils.month_start_end(year=today.year, month=today.month, rf='%Y%m%d')
    current_month_count = 0
    for article in records:
        article['article_type_zh'] = constant.ARTICLE_TYPES.get(article['article_type'], '')
        article['is_publish_zh'] = '是' if article['is_publish'] else '否'
        article['examine_zh'] = constant.EXAMINE_STATES.get(article['examine_state'])
        if not article['level_flag']:
            article['level_flag'] = '等待评级'
        if month_start <= str(article['create_date']) <= month_end:
            current_month_count += 1
    response_data = {
        'data': records,
        'count':  current_month_count
    }
    return AllResponse.operate_successfully(data=response_data)


class DeleteParamItem(BaseModel):
    start: Optional[datetime_parse.date]
    end: Optional[datetime_parse.date]
    aid: Optional[int]


@article_writing_api.delete('/my/')  # 用户删除文章记录
def delete_user_article(ruizy_token: str = Header(...), param: DeleteParamItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if param.start and param.end:
        # query_annex = """
        #     SELECT annex_url FROM work_article_writing WHERE user_id=%s AND create_date>=%s AND create_date<=%s;
        # """
        # sql = "DELETE FROM work_article_writing WHERE user_id=%s AND create_date>=%s AND create_date<=%s;"
        # prm = (person['uid'], param.start.strftime('%Y%m%d'), param.end.strftime('%Y%m%d'))
        # 20220721批量删除功能关闭
        pass
    else:
        pass  # 单独删除 移到全局
    if not param.aid:
        return AllResponse.validate_error(msg='参数错误!')
    # 单独删除数据
    query_annex = "SELECT annex_url,examine_state,level_flag FROM work_article_writing WHERE id=%s AND user_id=%s;"
    sql = "DELETE FROM work_article_writing WHERE id=%s AND user_id=%s;"
    prm = (param.aid, person['uid'])

    db_conn = FAConnection()
    records = db_conn.query(query_annex, param=prm, keep_conn=True)
    can_operate = True
    for r in records:
        if r['examine_state'] != 0 or r['level_flag'] != '':
            can_operate = False
    if not can_operate:
        db_conn.close()
        return AllResponse.forbidden_error(msg='已经审核或评级的文章不支持删除了...')
    remove_annexes = [obj['annex_url'] for obj in records]
    count, success = db_conn.execute(sql, param=prm)
    if success:
        for file in remove_annexes:
            if not file:
                continue
            annex_file = pathlib.Path(FILE_STORAGE).joinpath(file)
            if annex_file.is_file():
                annex_file.unlink()
    rep_msg = '操作成功，删除了{}条数据!'.format(count)
    return AllResponse.operate_successfully(msg=rep_msg) if success else AllResponse.server_error()


@article_writing_api.get('/statistics/')  # 文章记录统计
def statistics_special_article(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                               st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    # 查询数据
    sql = """
        SELECT a.id,a.create_date,a.user_id,a.title,a.article_type,a.media_name,a.rough_type,a.words,a.examine_state,
            a.examiner,a.examine_explain,a.allowance,a.partner,a.annex,a.annex_url,a.is_publish,a.note,
            a.level_flag,a.level_explain,b.admin_name 
        FROM work_article_writing As a
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.examine_state=1 AND a.create_date>=%s AND a.create_date<=%s AND a.is_active=1;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=(start_date, end_date))

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))

    # 数据统计
    user_dict = {}
    for item in records:
        item['article_type_zh'] = constant.ARTICLE_TYPES.get(item['article_type'], '')
        item['is_publish_zh'] = '是' if item['is_publish'] else '否'
        # 外发文章期货日报的量
        qhrb_count = 0
        if item['article_type'] == 'T03' and item['media_name'].strip() == '期货日报':
            qhrb_count = 1
        if item['user_id'] in user_dict.keys():
            data_obj = user_dict[item['user_id']]
            data_obj['levels'].append(item['level_flag'])
            data_obj['article_types'].append(item['article_type'])
            data_obj['total_count'] += 1
            data_obj['qhrb_count'] += qhrb_count
        else:
            user_dict[item['user_id']] = {
                'user_id': item['user_id'],
                'admin_name': item['admin_name'],
                'levels': [item['level_flag']],
                'article_types': [item['article_type']],
                'qhrb_count': qhrb_count,
                'total_count': 1
            }

    statistics_data = []
    total_levels = []
    total_obj = {
        'user_id': -1,
        'admin_name': '合计',
        'total_count': 0
    }
    for _, v in user_dict.items():
        # 评级
        v['levels'].sort()  # 排序ABC
        total_levels.extend(v['levels'])
        level_count = Counter(v['levels']).items()
        v['level_count'] = ''.join(['%d%s' % (v, k) for k, v in level_count if k])
        # 类别
        type_count = Counter(v['article_types']).items()
        for tk, tv in type_count:
            if tk:
                v[tk] = tv
            if tk in total_obj.keys():  # 计算各类别文章合计
                total_obj[tk] += tv
            else:
                total_obj[tk] = tv
        total_obj['total_count'] += v['total_count']
        del v['article_types']
        del v['levels']
        # 计算除期货日报外其他外发文章的数量
        if v.get('T03'):
            v['T03_other'] = v['T03'] - v['qhrb_count']
            if v['T03_other'] == 0:
                v['T03_other'] = ''
        if v['qhrb_count'] == 0:
            v['qhrb_count'] = ''
        statistics_data.append(v)
    statistics_data.sort(key=lambda x: x['total_count'], reverse=True)
    # 加入合计
    if len(statistics_data) > 0:
        total_levels.sort()
        total_obj['level_count'] = ''.join(['%d%s' % (v, k) for k, v in Counter(total_levels).items() if k])
        statistics_data.append(total_obj)
    response_data = {
        'statistics': statistics_data,
        'records': records
    }
    return AllResponse.operate_successfully(data=response_data)


@article_writing_api.get('/statisticsMonthly/')  # 按月统计文章数量
def statistics_task_monthly(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                            st: int = Query(1, ge=1, le=2)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    # 查询数据
    sql = """
        SELECT a.id,a.create_date,a.user_id,a.title,a.article_type,a.rough_type,a.words,a.examine_state,
            a.examiner,a.examine_explain,a.allowance,a.partner,a.annex,a.annex_url,a.is_publish,a.note,
            a.level_flag,a.level_explain,b.admin_name 
        FROM work_article_writing As a
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.examine_state=1 AND a.create_date>=%s AND a.create_date<=%s AND a.is_active=1 ;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=(start_date, end_date))

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if st == 2:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if st == 2 and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))

    month_list = datetime_utils.month_of_range(start=start_date, end=end_date, ft='%Y%m%d')
    # 对数据按人员按月份统计
    statistics_data, table_columns = work_utils.statistics_monthly(records, month_list=month_list)
    response_data = {
        'statistics_data': statistics_data,
        'table_columns': table_columns
    }
    return AllResponse.operate_successfully(data=response_data)
